﻿using System;
using System.Collections.Generic;
using System.EnterpriseServices;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web.UI.WebControls;
using ClassLibrary;


namespace WebApp
{
    public class DataAccess
    {
        SqlConnection conn = new SqlConnection();
        private SqlCommand cmd;
        private SqlDataAdapter da;
        private DataSet ds;
        private SqlTransaction tr;
        private SqlCommandBuilder cb;

        public DataAccess()
        {

        }
        //Database connectie
        #region
        public string connectionString()          // haalt de connectiestring uit Web.Config
        {
            try
            {
                return ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void storedProcedureCall(string strStoredProcedure)
        {
            try
            {

                SqlConnection conn;
                conn = new SqlConnection();
                conn.ConnectionString = (connectionString());

                cmd = new SqlCommand();
                da = new SqlDataAdapter();
                cb = new SqlCommandBuilder();
                ds = new DataSet();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strStoredProcedure;
                cmd.Connection = conn;

                conn.Open();

                tr = conn.BeginTransaction();
                cmd.Transaction = tr;
            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
            }
            finally
            {
                closeCon();
            }



        }
        public void closeCon()
        {
            try
            {

                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
            }



        }
        #endregion
        public DataSet getCompany()
        {
            try
            {


                storedProcedureCall("SP_getCompany");
                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                da.Fill(ds, "CompanyTabel");

            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }
            finally
            {
                closeCon();
            }
            return ds;
        }

        public DataSet getContractFormulas()
        {
            try
            {


                storedProcedureCall("SP_getContractFormulas");
                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                da.Fill(ds, "FormulaTable");

            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }
            finally
            {
                closeCon();
            }
            return ds;
        }

        public DataSet getAllReservations()
        {
            try
            {


                storedProcedureCall("SP_getAllReservations");
                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                da.Fill(ds, "Reservationtabel");

            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }
            finally
            {
                closeCon();
            }
            return ds;
        }

        public DataSet getAllEvents()
        {
            try
            {


                storedProcedureCall("SP_getAllEvents");
                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                da.Fill(ds, "eventsTable");

            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }
            finally
            {
                closeCon();
            }
            return ds;
        }  
        public bool removeCompany(int Id)
        {
            try
            {

                storedProcedureCall("SP_removeCompany");
                cmd.Parameters.AddWithValue("Id", Id);

                cmd.ExecuteNonQuery();
                tr.Commit();

            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
                
            }
            finally
            {
                closeCon();
            }
            return true;
        }  // een bool wordt gereturned zodat er een controle kan komen of de methode wel of niet geslaagd is

        public bool removeContract(int id)
        {
            try
            {

                storedProcedureCall("SP_removeContract");
                cmd.Parameters.AddWithValue("Id", id);

                cmd.ExecuteNonQuery();
                tr.Commit();

            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);

            }
            return true;
        }  // een bool wordt gereturned zodat er een controle kan komen of de methode wel of niet geslaagd is

        public bool cancelContract(int id)
        {
            bool value = false;
            try
            {
                DateTime endDate = DateTime.Now;
                storedProcedureCall("SP_cancellContract");
                cmd.Parameters.AddWithValue("Id", id);
                cmd.Parameters.AddWithValue("endDate", endDate);

                cmd.ExecuteNonQuery();
                tr.Commit();
                value = true;
            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
            }
            finally
            {
                closeCon();
            }
            //value = true indien try gedeelte succesvol was
            return value;
        }

        public bool registerContract(DateTime startDate, DateTime endDate, int compid, int conformId, int price)
        {
            try
            {
                storedProcedureCall("SP_registerContract");

                cmd.Parameters.AddWithValue("conStart", startDate);
                cmd.Parameters.AddWithValue("conEnd", endDate);
                cmd.Parameters.AddWithValue("conCompID", compid);
                cmd.Parameters.AddWithValue("conFormulaID", conformId);
                cmd.Parameters.AddWithValue("conPrice", price);

                cmd.ExecuteNonQuery();
                tr.Commit();

            }
            catch (Exception ex)
            {

                Logging.Log(ex.Message + ex.StackTrace);
            }
            try
            {
                closeCon();
            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);

            }

            return true;
        }


        public bool updateContract(int id, DateTime startDate, DateTime endDate, int compid, int conformId)
        {
            try
            {
                storedProcedureCall("SP_updateContract");

                cmd.Parameters.AddWithValue("conId", id);
                cmd.Parameters.AddWithValue("conStart", startDate);
                cmd.Parameters.AddWithValue("conEnd", endDate);
                cmd.Parameters.AddWithValue("conCompId", compid);
                cmd.Parameters.AddWithValue("conFormId", conformId);


                cmd.ExecuteNonQuery();
                tr.Commit();

            }
            catch (Exception ex)
            {

                Logging.Log(ex.Message + ex.StackTrace);
            }
            try
            {
                closeCon();
            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);


            }

            return true;
        }

        public DataSet companyInfo(int Id)
        {
            try
            {

                storedProcedureCall("SP_getCompanyInfo");

                cmd.Parameters.AddWithValue("Id", Id);
                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                da.Fill(ds, "CompanyTabel");

            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }
            finally
            {
                closeCon();
            }
            
            return ds;
        }  

        public DataSet getReservation(DateTime date)
        {
            try
            {

                storedProcedureCall("SP_getReservation");

                cmd.Parameters.AddWithValue("date", date );
                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                da.Fill(ds, "ReservationTabel");

            }
            catch (Exception ex)
            {
                 Logging.Log(ex.Message + ex.StackTrace);
                 ds = null;
            }
            finally
            {
                closeCon();
            }
  
            return ds;
        }

        public DataSet getContractsByCompayId(int id)
        {
            try
            {

                storedProcedureCall("SP_getContractsByCompanyId");

                cmd.Parameters.AddWithValue("id", id);
                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                if (da == null)
                {

                }
                else
                {
                    da.Fill(ds, "Contracten");
                }

            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }
            finally
            {
                closeCon();
            }

            return ds;
        }

        public DataSet getAllContract()
        {
            try
            {

                storedProcedureCall("SP_getContracts");

                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                da.Fill(ds, "AllContracts");

            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }
            finally
            {
                closeCon();
            }

            return ds;
        }

        public DataSet getUsernameReservation(int id)
        {
            try
            {

                storedProcedureCall("SP_getUsernameReservation");

                cmd.Parameters.AddWithValue("id", id);
                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                da.Fill(ds, "ReservationTabel");

            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }
            finally
            {
                closeCon();
            }

            return ds;
        }


        public bool insertCompany(string name, string street, int zipcode, string city, string country, string email, int phone, string employees )
        {
            try 
	        {
                storedProcedureCall("SP_registerCompany");

                cmd.Parameters.AddWithValue("name", name);
                cmd.Parameters.AddWithValue("street", street);
                cmd.Parameters.AddWithValue("zipcode", zipcode);
                cmd.Parameters.AddWithValue("city", city);
                cmd.Parameters.AddWithValue("country", country);
                cmd.Parameters.AddWithValue("email", email);
                cmd.Parameters.AddWithValue("phone", phone);
                cmd.Parameters.AddWithValue("employees", employees);

                cmd.ExecuteNonQuery();
                tr.Commit();

	        }
	        catch (Exception ex )
	        {

                Logging.Log(ex.Message + ex.StackTrace);
	        }
            try
            {
                closeCon();
            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
                
            }

            return true;
        }

        public bool updateCompany(int id, string name, string street, int zipcode, string city, string country, string email, int phone, string employees)
        {
            try
            {
                storedProcedureCall("SP_updateCompany");

                cmd.Parameters.AddWithValue("id", id);
                cmd.Parameters.AddWithValue("name", name);
                cmd.Parameters.AddWithValue("street", street);
                cmd.Parameters.AddWithValue("zipcode", zipcode);
                cmd.Parameters.AddWithValue("city", city);
                cmd.Parameters.AddWithValue("country", country);
                cmd.Parameters.AddWithValue("email", email);
                cmd.Parameters.AddWithValue("phone", phone);
                cmd.Parameters.AddWithValue("employees", employees);

                cmd.ExecuteNonQuery();
                tr.Commit();

            }
            catch (Exception ex)
            {

                Logging.Log(ex.Message + ex.StackTrace);
            }
            try
            {
                closeCon();
            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);


            }

            return true;
        }


        public int insertReservation(DateTime start, DateTime end, int comp, string loc)
        {
            int Id = 0;
            try
            {
                Object returnId;
                DateTime createdate = DateTime.Now;
                storedProcedureCall("SP_insertReservation");

                cmd.Parameters.AddWithValue("start", start);
                cmd.Parameters.AddWithValue("end", end);
                cmd.Parameters.AddWithValue("company", comp);
                cmd.Parameters.AddWithValue("location", loc);
                cmd.Parameters.AddWithValue("createdate", createdate);


                returnId = cmd.ExecuteScalar();
                Id = Convert.ToInt32(returnId);
                tr.Commit();

            }
            catch (Exception ex)
            {

                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }
            finally
            {
                closeCon();
            }

            return Id;
        }

        public bool insertContract(int id, DateTime start, DateTime end, int compId, int conformId)
        {
            try
            {
                DateTime createdate = DateTime.Now;
                storedProcedureCall("SP_registerContract");

                cmd.Parameters.AddWithValue("conId", id);
                cmd.Parameters.AddWithValue("conStart", start);
                cmd.Parameters.AddWithValue("conEnd", end);
                cmd.Parameters.AddWithValue("conDescription", compId);
                cmd.Parameters.AddWithValue("conPrice", conformId);


                cmd.ExecuteNonQuery();
                tr.Commit();

            }
            catch (Exception ex)
            {

                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }
            finally
            {
                closeCon();
            }

            return true;
        }

        //Contract wijziging opslaan
        public bool eventContract(int Event, String userName, int contractId)
        {
            try
            {
                DateTime eventTime = DateTime.Now;
                storedProcedureCall("SP_eventContract");

                cmd.Parameters.AddWithValue("Event", Event);
                cmd.Parameters.AddWithValue("UserName", userName);
                cmd.Parameters.AddWithValue("ContractId", contractId);
                cmd.Parameters.AddWithValue("EventTime", eventTime);

                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }

            finally
            {
                closeCon();
            }

            return true;
        }

        //Reservatie wijziging opslaan
        public bool eventReservation(int Event, String userName, String room, DateTime bookTime )
        {
            try
            {
                DateTime eventTime = DateTime.Now;
                storedProcedureCall("SP_eventReservation");

                cmd.Parameters.AddWithValue("Event", Event);
                cmd.Parameters.AddWithValue("UserName", userName);
                cmd.Parameters.AddWithValue("Room", room);
                cmd.Parameters.AddWithValue("EventTime", eventTime);
                cmd.Parameters.AddWithValue("BookTime", bookTime);

                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception ex)
            {
                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }

            finally
            {
                closeCon();
            }

            return true;
        }


        //Contract met het reservatie bedrijf kan opgezegd worden
        public bool deleteContract(int id)
        {
            try
            {

                storedProcedureCall("SP_removeContract");

                cmd.Parameters.AddWithValue("conNumber", id);

                cmd.ExecuteNonQuery();
                tr.Commit();

            }
            catch (Exception ex)
            {

                Logging.Log(ex.Message + ex.StackTrace);
            }

            try
            {
                closeCon();
            }
            catch (Exception ex)
            {


                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }

            return true;
        }

        public bool deleteReservation(int id)
        {
            try
            {

                storedProcedureCall("SP_removeReservation");

                cmd.Parameters.AddWithValue("id", id);

                cmd.ExecuteNonQuery();
                tr.Commit();

            }
            catch (Exception ex)
            {

                Logging.Log(ex.Message + ex.StackTrace);
            }

            try
            {
                closeCon();
            }
            catch (Exception ex)
            {


                Logging.Log(ex.Message + ex.StackTrace);
                ds = null;
            }

            return true;
        }

    }
}